package com.hjy.cabinet.db;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import com.hjy.cabinet.db.domain.UserSyncVo;

import java.util.ArrayList;
import java.util.List;


public class UserbaseHelper extends SQLiteOpenHelper {
    private static final String DATABASE_NAME = "cabinet_app_database.db";

    private static final int DATABASE_VERSION = 31;
    // 表名和列名
    private static final String TABLE_USERS = "users";
    private static final String COLUMN_ID = "id";
    private static final String COLUMN_FULL_NAME = "fullName";
    private static final String COLUMN_USER_NAME = "userName";
    private static final String COLUMN_PASSWORD = "password";
    private static final String COLUMN_ROLE_ID = "roleId";
    private static final String COLUMN_SEX = "sex";
    private static final String COLUMN_TEL = "tel";
    private static final String COLUMN_ORG_ID = "orgId";
    private static final String COLUMN_STATUS = "status";
    private static final String COLUMN_AVATAR = "avatar";
    private static final String COLUMN_UPDATE_BY = "updateBy";
    private static final String COLUMN_UPDATE_USER_NAME = "updateUserName";
    private static final String COLUMN_UPDATE_TIME = "updateTime";
    private static final String COLUMN_ORG_NAME = "orgName";
    private static final String COLUMN_IDS = "ids";
    private static final String COLUMN_OLD_PASSWORD = "oldPassword";
    private static final String COLUMN_NEW_PASSWORD = "newPassword";
    private static final String COLUMN_ID_CARD = "idCard";

    private static final String DELETE_STATUS = "deleteStatus";
    public UserbaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        // 创建users表的SQL语句
        String CREATE_USERS_TABLE = "CREATE TABLE " + TABLE_USERS + "("
                + COLUMN_ID + " INTEGER PRIMARY KEY,"  // 改为普通主键
                + COLUMN_FULL_NAME + " TEXT,"
                + COLUMN_USER_NAME + " TEXT NOT NULL,"
                + COLUMN_PASSWORD + " TEXT NOT NULL,"
                + COLUMN_ROLE_ID + " TEXT,"
                + COLUMN_SEX + " TEXT,"
                + COLUMN_TEL + " TEXT,"
                + COLUMN_ORG_ID + " INTEGER,"
                + COLUMN_STATUS + " INTEGER,"
                + COLUMN_AVATAR + " TEXT,"
                + COLUMN_UPDATE_BY + " INTEGER,"
                + COLUMN_UPDATE_USER_NAME + " TEXT,"
                + COLUMN_UPDATE_TIME + " INTEGER,"
                + COLUMN_ORG_NAME + " TEXT,"
                + COLUMN_IDS + " TEXT,"
                + COLUMN_OLD_PASSWORD + " TEXT,"
                + COLUMN_NEW_PASSWORD + " TEXT,"
                + DELETE_STATUS + " TEXT,"
                + COLUMN_ID_CARD + " TEXT" + ")";

        // 执行创建表的SQL语句
        db.execSQL(CREATE_USERS_TABLE);

        // 插入默认数据
        ContentValues values = new ContentValues();
        values.put(COLUMN_FULL_NAME, "系统管理员");
        values.put(COLUMN_USER_NAME, "admin");
        values.put(COLUMN_PASSWORD, "613D1EECDE6D683728021322F919D5E753B153E0D6C218F1AAF514A591C4F466");
        values.put(COLUMN_ROLE_ID, "3565d834f28f483380cd9df96d283382");
        values.put(COLUMN_SEX, "男");
        values.put(COLUMN_TEL, "18112345678");
        values.put(COLUMN_ORG_ID, 1); // 假设orgId为1，根据实际情况修改
        values.put(COLUMN_STATUS, 1);
        values.put(COLUMN_AVATAR, "/upload/20240813/jpg/483d3f157d684a9ab19a21139ee21c08.jpg");
        values.put(COLUMN_UPDATE_BY, 0); // 假设updateBy为0，根据实际情况修改
        values.put(COLUMN_UPDATE_USER_NAME, ""); // 假设updateUserName为空，根据实际情况修改
        values.put(COLUMN_UPDATE_TIME, System.currentTimeMillis()); // 使用当前时间戳
        values.put(COLUMN_ORG_NAME, ""); // 假设orgName为空，根据实际情况修改
        values.put(COLUMN_OLD_PASSWORD, ""); // 假设oldPassword为空，根据实际情况修改
        values.put(COLUMN_NEW_PASSWORD, ""); // 假设newPassword为空，根据实际情况修改
        values.put(DELETE_STATUS, "0"); // 假设deleteStatus为0，表示未删除
        values.put(COLUMN_ID_CARD, "0503747696"); // 假设idCard为空，根据实际情况修改

        db.insert(TABLE_USERS, null, values);
        Log.d("DatabaseHelper", "Users table created and default data inserted.");
    }




    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_USERS);
        onCreate(db);
    }

    public List<UserSyncVo> getAllUsers() {
        List<UserSyncVo> userList = new ArrayList<>();
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.query(TABLE_USERS, null, null, null, null, null, null);

        if (cursor.moveToFirst()) {
            do {
                UserSyncVo user = new UserSyncVo();
                user.setId(cursor.getInt(cursor.getColumnIndexOrThrow(COLUMN_ID)));
                user.setFullName(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_FULL_NAME)));
                user.setUserName(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_USER_NAME)));
                user.setPassword(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_PASSWORD)));
                user.setRoleId(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_ROLE_ID)));
                user.setSex(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_SEX)));
                user.setTel(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_TEL)));
                user.setOrgId(cursor.getInt(cursor.getColumnIndexOrThrow(COLUMN_ORG_ID)));
                user.setStatus(cursor.getInt(cursor.getColumnIndexOrThrow(COLUMN_STATUS)) == 1);
                user.setAvatar(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_AVATAR)));
                user.setUpdateBy(cursor.getInt(cursor.getColumnIndexOrThrow(COLUMN_UPDATE_BY)));
                user.setUpdateUserName(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_UPDATE_USER_NAME)));
                user.setUpdateTime(cursor.getLong(cursor.getColumnIndexOrThrow(COLUMN_UPDATE_TIME)));
                user.setOrgName(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_ORG_NAME)));
                user.setIds(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_IDS)));
                user.setOldPassword(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_OLD_PASSWORD)));
                user.setNewPassword(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_NEW_PASSWORD)));
                user.setIdCard(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_ID_CARD)));
                user.setDeleteStatus(cursor.getString(cursor.getColumnIndexOrThrow(DELETE_STATUS)).equals("1"));

                userList.add(user);
            } while (cursor.moveToNext());
        }

        cursor.close();
        db.close();
        return userList;
    }


    public List<UserSyncVo> getUsersPage(String userNameSelect, int offset, int limit) {
        SQLiteDatabase db = this.getReadableDatabase();
        List<UserSyncVo> userSyncs = new ArrayList<>();

        // Basic query
        StringBuilder queryBuilder = new StringBuilder("SELECT * FROM " + TABLE_USERS + " WHERE 1=1");

        // Add conditions
        List<String> selectionArgs = new ArrayList<>();

        if (userNameSelect != null && !userNameSelect.isEmpty()) {
            queryBuilder.append(" AND " + COLUMN_FULL_NAME + " LIKE ?");
            selectionArgs.add("%" + userNameSelect + "%");
        }

        // Filter for deleteStatus = false (assuming 0 represents false)
        queryBuilder.append(" AND " + DELETE_STATUS + " = 0");

        // Order by created_at column in descending order
        queryBuilder.append(" ORDER BY " + COLUMN_UPDATE_TIME + " DESC, " + COLUMN_ID + " DESC LIMIT ? OFFSET ?");
        selectionArgs.add(String.valueOf(limit));
        selectionArgs.add(String.valueOf(offset));

        // Execute query
        Cursor cursor = db.rawQuery(queryBuilder.toString(), selectionArgs.toArray(new String[0]));

        if (cursor.moveToFirst()) {
            int idIndex = cursor.getColumnIndexOrThrow(COLUMN_ID);
            int fullNameIndex = cursor.getColumnIndexOrThrow(COLUMN_FULL_NAME);
            int userNameIndex = cursor.getColumnIndexOrThrow(COLUMN_USER_NAME);
            int passwordIndex = cursor.getColumnIndexOrThrow(COLUMN_PASSWORD);
            int roleIdIndex = cursor.getColumnIndexOrThrow(COLUMN_ROLE_ID);
            int sexIndex = cursor.getColumnIndexOrThrow(COLUMN_SEX);
            int telIndex = cursor.getColumnIndexOrThrow(COLUMN_TEL);
            int orgIdIndex = cursor.getColumnIndexOrThrow(COLUMN_ORG_ID);
            int statusIndex = cursor.getColumnIndexOrThrow(COLUMN_STATUS);
            int avatarIndex = cursor.getColumnIndexOrThrow(COLUMN_AVATAR);
            int updateByIndex = cursor.getColumnIndexOrThrow(COLUMN_UPDATE_BY);
            int updateUserNameIndex = cursor.getColumnIndexOrThrow(COLUMN_UPDATE_USER_NAME);
            int orgNameIndex = cursor.getColumnIndexOrThrow(COLUMN_ORG_NAME);
            int idsIndex = cursor.getColumnIndexOrThrow(COLUMN_IDS);
            int oldPasswordIndex = cursor.getColumnIndexOrThrow(COLUMN_OLD_PASSWORD);
            int newPasswordIndex = cursor.getColumnIndexOrThrow(COLUMN_NEW_PASSWORD);
            int idCardIndex = cursor.getColumnIndexOrThrow(COLUMN_ID_CARD);
            int deleteStatusIndex = cursor.getColumnIndexOrThrow(DELETE_STATUS);
            int updateTime = cursor.getColumnIndexOrThrow(COLUMN_UPDATE_TIME);


            do {
                Integer id = cursor.getInt(idIndex);
                String fullName = cursor.getString(fullNameIndex);
                String userName = cursor.getString(userNameIndex);
                String password = cursor.getString(passwordIndex);
                String roleId = cursor.getString(roleIdIndex);
                String sex = cursor.getString(sexIndex);
                String tel = cursor.getString(telIndex);
                Integer orgIdValue = cursor.getInt(orgIdIndex);
                Boolean statusValue = cursor.getInt(statusIndex) > 0; // Assuming 1 for true, 0 for false
                String avatar = cursor.getString(avatarIndex);
                Integer updateBy = cursor.getInt(updateByIndex);
                String updateUserName = cursor.getString(updateUserNameIndex);
                String orgName = cursor.getString(orgNameIndex);
                String ids = cursor.getString(idsIndex);
                String oldPassword = cursor.getString(oldPasswordIndex);
                String newPassword = cursor.getString(newPasswordIndex);
                String idCard = cursor.getString(idCardIndex);
                Boolean deleteStatus = cursor.getInt(deleteStatusIndex) > 0; // Assuming 1 for true, 0 for false

                UserSyncVo userSync = new UserSyncVo();
                userSync.setId(id);
                userSync.setFullName(fullName);
                userSync.setUserName(userName);
                userSync.setPassword(password);
                userSync.setRoleId(roleId);
                userSync.setSex(sex);
                userSync.setTel(tel);
                userSync.setOrgId(orgIdValue);
                userSync.setStatus(statusValue);
                userSync.setAvatar(avatar);
                userSync.setUpdateBy(updateBy);
                userSync.setUpdateUserName(updateUserName);
                userSync.setOrgName(orgName);
                userSync.setIds(ids);
                userSync.setOldPassword(oldPassword);
                userSync.setNewPassword(newPassword);
                userSync.setIdCard(idCard);
                userSync.setDeleteStatus(deleteStatus);
                userSync.setUpdateTime(cursor.getLong(updateTime));
                userSyncs.add(userSync);
            } while (cursor.moveToNext());
        }

        cursor.close();
        db.close();
        return userSyncs;
    }

}